package com.ssyt.hiring.talent.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.ssyt.hiring.common.PageModel;
import com.ssyt.hiring.common.util.DBUtil;
import com.ssyt.hiring.company.entity.ApplicationEntity;
import com.ssyt.hiring.talent.entity.ResumeEntity;

public class ResumeDao {

	/**
	 * 简历的查询(分页查询)
	 * 
	 * @return
	 */
	public PageModel<ResumeEntity> listResume(int id, int pageNo, int pageSize) {
		List<ResumeEntity> resumeList = new ArrayList<>();
		Connection connection = DBUtil.getConnection();
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		String sql = "SELECT id,intention,work_experience,project_experience FROM resume WHERE talent_id=? ORDER BY id DESC LIMIT ?,?";
		try {
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, id);
			preparedStatement.setInt(2, (pageNo - 1) * pageSize);
			preparedStatement.setInt(3, pageSize);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				ResumeEntity resume = new ResumeEntity();
				resume.setId(resultSet.getInt("id"));
				resume.setIntention(resultSet.getString("intention"));
				resume.setWorkExperience(resultSet.getString("work_experience"));
				resume.setProjectExperience(resultSet
						.getString("project_experience"));
				resumeList.add(resume);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(resultSet, preparedStatement, connection);
		}
		return new PageModel<>(resumeList, getTotalRecords(id), pageNo,
				pageSize);
		// List<ResumeEntity> listResume =
		// DBUtil.executeQuery(ResumeEntity.class,
		// sql, id);
		// return listResume;

	}

	/**
	 * 查询总共有多少条简历信息
	 * 
	 * @return
	 */
	private int getTotalRecords(int id) {
		int totalRecords = 0;
		Connection connection = DBUtil.getConnection();
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		String sql = "SELECT COUNT(*) AS'totalRecords' FROM resume WHERE talent_id=?";
		try {
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, id);
			resultSet = preparedStatement.executeQuery();
			resultSet.next();
			totalRecords = resultSet.getInt("totalRecords");
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(resultSet, preparedStatement, connection);
		}
		return totalRecords;
	}

	/**
	 * 简历的删除
	 * 
	 * @param id
	 * @return
	 */
	public int daleteResume(int id) {
		String sql = "DELETE FROM resume WHERE id=?";
		return DBUtil.executeUpdate(sql, id);
	}

	/**
	 * 简历的新增
	 * 
	 * @param id
	 * @return
	 */
	public int addResume(ResumeEntity resumeEntity) {
		String sql = "INSERT INTO resume VALUES(NULL,?,?,?,?)";
		return DBUtil.executeUpdate(sql, resumeEntity.getTalentId(),
				resumeEntity.getIntention(), resumeEntity.getWorkExperience(),
				resumeEntity.getProjectExperience());
	}

	/**
	 * 简历的修改
	 * 
	 * @param id
	 * @return
	 */
	public int updateResume(ResumeEntity resumeEntity) {
		String sql = "UPDATE resume SET intention=?, work_experience=?, project_experience=? WHERE id=?";
		return DBUtil.executeUpdate(sql, resumeEntity.getIntention(),
				resumeEntity.getWorkExperience(),
				resumeEntity.getProjectExperience(), resumeEntity.getId());
	}

	/**
	 * 简历的详情
	 * 
	 * @param id
	 * @return
	 */
	public ResumeEntity list(int id) {
		String sql = "SELECT id,intention,work_experience,project_experience FROM resume WHERE id=?";
		return DBUtil.getUniqueResult(ResumeEntity.class, sql, id);
	}

	/**
	 * 应聘信息
	 * 
	 * @param id
	 * @return
	 */
	public List<ApplicationEntity> application(int id) {
		String sql = "SELECT a.id,c.name as 'company$name',p.name as 'position$name',a.app_time,a.status FROM company c,position p,application a WHERE c.id=p.company_id and p.id=a.position_id and a.talent_id=? ";
		List<ApplicationEntity> application = DBUtil.executeQuery(
				ApplicationEntity.class, sql, id);
		return application;
	}

	/**
	 * 删除投出的简历
	 * 
	 * @param id
	 * @return
	 */
	public int daleteApplication(int id) {
		String sql = "DELETE FROM application WHERE id=?";
		return DBUtil.executeUpdate(sql, id);
	}

	public List<ResumeEntity> resumeList(int talentId) {
		String sql = "SELECT id,intention,work_experience,project_experience FROM resume WHERE id=?";
		return DBUtil.executeQuery(ResumeEntity.class, sql, talentId);
	}
}
